if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_GRN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_GRN]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_GRNItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_GRNItem]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Invoice_GRNPacking]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Invoice_GRNPacking]
GO

CREATE TABLE [dbo].[Invoice_GRN] (
	[Inv_GRNID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[Inv_GRNDate] [datetime] NOT NULL ,
	[Inv_GRNVNo] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SessionID] [smallint] NOT NULL ,
	[Remarks] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReadOnly] [bit] NOT NULL ,
	[LoginID] [int] NOT NULL ,
	[HostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EntryDateTime] [datetime] NULL ,
	[ModifyID] [int] NULL ,
	[ModifyHostName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ModifyDateTime] [datetime] NULL ,
	[BEDate] [datetime] NULL ,
	[SupplierID] [int] NULL ,
	[ShipID] [smallint] NULL ,
	[ShipLineID] [smallint] NULL ,
	[ContainerTypeID] [smallint] NULL ,
	[BENo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[VoyNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Currency] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ContainerNo] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[NoOfContainers] [decimal](11, 2) NOT NULL ,
	[SumQTY] [numeric](15, 4) NOT NULL ,
	[SumNetWT] [decimal](15, 4) NOT NULL ,
	[SumGrossWT] [decimal](15, 4) NOT NULL ,
	[SumGWTotal] [decimal](15, 4) NOT NULL ,
	[SumNetTotal] [numeric](15, 4) NOT NULL ,
	[SumAmount] [numeric](15, 4) NOT NULL ,
	[NetInvoice] [decimal](15, 4) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_GRNItem] (
	[inv_GRNItemID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[inv_GRNID] [bigint] NOT NULL ,
	[ItemID] [smallint] NULL ,
	[VarietyID] [smallint] NULL ,
	[LQTY] [numeric](15, 4) NOT NULL ,
	[Type] [tinyint] NOT NULL ,
	[ContainerNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Invoice_GRNPacking] (
	[inv_GRNPackID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[inv_GRNID] [bigint] NOT NULL ,
	[ShipMarkID] [smallint] NULL ,
	[PackageID] [smallint] NULL ,
	[ItemID] [smallint] NULL ,
	[Qty] [numeric](15, 4) NOT NULL ,
	[NetWT] [decimal](15, 4) NOT NULL ,
	[GrossWT] [decimal](15, 4) NOT NULL ,
	[GWTotal] [decimal](15, 4) NOT NULL ,
	[NetTotal] [numeric](15, 4) NOT NULL ,
	[Rate] [numeric](15, 4) NOT NULL ,
	[Amount] [numeric](15, 4) NOT NULL ,
	[Type] [tinyint] NOT NULL ,
	[ItemRemarks] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RateRemarks] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RateType] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PRemarks] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PAmount] [numeric](15, 4) NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Invoice_GRN] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_GRN] PRIMARY KEY  CLUSTERED 
	(
		[Inv_GRNID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_GRNItem] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_GRNItem] PRIMARY KEY  CLUSTERED 
	(
		[inv_GRNItemID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_GRNPacking] WITH NOCHECK ADD 
	CONSTRAINT [PK_Invoice_GRNPacking] PRIMARY KEY  CLUSTERED 
	(
		[inv_GRNPackID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Invoice_GRN] ADD 
	CONSTRAINT [DF_Invoice_GRN_ReadOnly] DEFAULT (0) FOR [ReadOnly],
	CONSTRAINT [DF_Invoice_GRN_HostName] DEFAULT (host_name()) FOR [HostName],
	CONSTRAINT [DF_Invoice_GRN_EntryDateTime] DEFAULT (getdate()) FOR [EntryDateTime],
	CONSTRAINT [DF_Invoice_GRN_NoOfContainers] DEFAULT (0) FOR [NoOfContainers],
	CONSTRAINT [DF_Invoice_GRN_SumQTY] DEFAULT (0) FOR [SumQTY],
	CONSTRAINT [DF_Invoice_GRN_SumGrossWT] DEFAULT (0) FOR [SumGrossWT],
	CONSTRAINT [DF_Invoice_GRN_SumGWTotal] DEFAULT (0) FOR [SumGWTotal],
	CONSTRAINT [DF_Invoice_GRN_SumNetTotal] DEFAULT (0) FOR [SumNetTotal],
	CONSTRAINT [DF_Invoice_GRN_SumAmount] DEFAULT (0) FOR [SumAmount],
	CONSTRAINT [DF_Invoice_GRN_NetInvoice] DEFAULT (0) FOR [NetInvoice]
GO

ALTER TABLE [dbo].[Invoice_GRNPacking] ADD 
	CONSTRAINT [DF_Invoice_GRNPacking_Rate] DEFAULT (0) FOR [Rate],
	CONSTRAINT [DF_Invoice_GRNPacking_PAmount] DEFAULT (0) FOR [PAmount]
GO